package com.glowdayz.glowmee.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.glowdayz.common.DBHandler;
import com.glowdayz.common.DateTime;
import com.glowdayz.common.PageHandler;
import com.glowdayz.common.MultipartBox.FileInfo;
import com.glowdayz.glowmee.cmd.HotEditerCMD;
import com.glowdayz.glowmee.cmd.ProductCMD;

public class HotEditerDAO {
	
	static Logger logger = Logger.getLogger(HotEditerDAO.class);
	
	private static HotEditerDAO instance = null;
	
	DBHandler db = null;
	
	public static HotEditerDAO getInstance() {
		if (instance == null) {
			instance = new HotEditerDAO();
		}
		return instance;
	}
	
	public HotEditerDAO() {
		db = DBHandler.getInstance();
	}
	
	public int insertHotProduct(HotEditerCMD cmd) throws SQLException {
		logger.debug("[insertHotProduct] ---------- Method Start ----------");
		logger.debug("[insertHotProduct][PARAM] HotEditerCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		
		String sql = "INSERT INTO hotediter("
				+ "idProduct, idSecondCategory, hotEditerType, promotionText, isTop, isDisplay, start_date, end_date, create_date"
				+ ") VALUES (?, ?, ?, ?, ?, 1, ?, ?, ?)";
		PreparedStatement pstmt = null;
		try {
			if (cmd.getIsTop() == 1 && cmd.getHotEditerType().equals("HOT")) {
				String editSql = "UPDATE hotediter SET isTop=0 WHERE hotEditerType=?";
				pstmt = db.getPrepareStatement(editSql);
				pstmt.setString(1, cmd.getHotEditerType());
				pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			} else if (cmd.getIsTop() == 1 && cmd.getHotEditerType().equals("EDITER")) {
				String editSql = "UPDATE hotediter SET isTop=0 WHERE hotEditerType=? AND idSecondCategory=?";
				pstmt = db.getPrepareStatement(editSql);
				pstmt.setString(1, cmd.getHotEditerType());
				pstmt.setInt(2, cmd.getIdSecondCategory());
				pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			}
			
			pstmt = db.getPrepareStatement(sql);
			pstmt.setInt(1, cmd.getIdProduct());
			pstmt.setInt(2, cmd.getIdSecondCategory());
			pstmt.setString(3, cmd.getHotEditerType());
			pstmt.setString(4, cmd.getPromotionText());
			pstmt.setInt(5, cmd.getIsTop());
			pstmt.setString(6, cmd.getStart_date());
			pstmt.setString(7, cmd.getEnd_date());
			pstmt.setString(8, DateTime.getInstance().getNowDateTimeString());
			result = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
			
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[insertHotProduct] " + e.getLocalizedMessage());
			logger.error("[insertHotProduct] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertHotProduct][RETURN] result=" + result);
		logger.debug("[insertHotProduct] ---------- Method End ----------");
		
		return result;
	}
	
	public int updateHotEditer(HotEditerCMD cmd) throws SQLException {
		logger.debug("[updateHotEditer] ---------- Method Start ----------");
		logger.debug("[updateHotEditer][PARAM] HotEditerCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		
		String sql = "UPDATE hotediter "
				+ "SET isDisplay=?, promotionText=?, isTop=?, start_date=?, end_date=?, modified_date=? "
				+ "WHERE idProduct=? AND idSecondCategory=? AND hotEditerType=?";
		PreparedStatement pstmt = null;
		try {
			if (cmd.getIsTop() == 1 && cmd.getHotEditerType().equals("HOT")) {
				String editSql = "UPDATE hotediter SET isTop=0 WHERE hotEditerType=?";
				pstmt = db.getPrepareStatement(editSql);
				pstmt.setString(1, cmd.getHotEditerType());
				pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			} else if (cmd.getIsTop() == 1 && cmd.getHotEditerType().equals("EDITER")) {
				String editSql = "UPDATE hotediter SET isTop=0 WHERE hotEditerType=? AND idSecondCategory=?";
				pstmt = db.getPrepareStatement(editSql);
				pstmt.setString(1, cmd.getHotEditerType());
				pstmt.setInt(2, cmd.getIdSecondCategory());
				pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			}
			
			pstmt = db.getPrepareStatement(sql);
			pstmt.setInt(1, cmd.getIsDisplay());
			pstmt.setString(2, cmd.getPromotionText());
			pstmt.setInt(3, cmd.getIsTop());
			pstmt.setString(4, cmd.getStart_date());
			pstmt.setString(5, cmd.getEnd_date());
			pstmt.setString(6, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(7, cmd.getIdProduct());
			pstmt.setInt(8, cmd.getIdSecondCategory());
			pstmt.setString(9, cmd.getHotEditerType());
			result = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateHotEditer] " + e.getLocalizedMessage());
			logger.error("[updateHotEditer] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateHotEditer][RETURN] result=" + result);
		logger.debug("[updateHotEditer] ---------- Method End ----------");
		
		return result;
	}
	
	public int isDisplayHotEditer(HotEditerCMD cmd) throws SQLException {
		logger.debug("[isDisplayHotEditer] ---------- Method Start ----------");
		logger.debug("[isDisplayHotEditer][PARAM] HotEditerCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "UPDATE hotediter "
				+ "SET isDisplay=?, modified_date=? "
				+ "WHERE idProduct=? AND idSecondCategory=? AND hotEditerType=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIsDisplay());
			pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(3, cmd.getIdProduct());
			pstmt.setInt(4, cmd.getIdSecondCategory());
			pstmt.setString(5, cmd.getHotEditerType());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[isDisplayHotEditer] " + e.getLocalizedMessage());
			logger.error("[isDisplayHotEditer] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[isDisplayHotEditer][RETURN] result=" + result);
		logger.debug("[isDisplayHotEditer] ---------- Method End ----------");
		
		return result;
	}
	
	public HotEditerCMD getHotEditer(int pId, int sId, String hotEditerType) throws SQLException {
		logger.debug("[getHotEditer] ---------- Method Start ----------");
		logger.debug("[getHotEditer][PARAM] pId=" + pId);
		logger.debug("[getHotEditer][PARAM] sId=" + sId);
		logger.debug("[getHotEditer][PARAM] hotEditerType=" + hotEditerType);
		
		HotEditerCMD result = null;
		
		String sql = "SELECT h.isTop, b.brandTitle, f.firstCategoryText, h.idSecondCategory, s.secondCategoryText, h.idProduct, p.productTitle, "
				+ "h.promotionText, h.start_date, h.end_date, h.isDisplay "
				+ "FROM hotediter h, product p, brand b, firstcategory f,secondcategory s "
				+ "WHERE h.idSecondCategory=s.idSecondCategory AND h.idProduct=p.idProduct AND p.idBrand=b.idBrand AND "
				+ "s.idFirstCategory=f.idFirstCategory AND h.idProduct=? AND s.idSecondCategory=? AND h.hotEditerType=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, pId);
			pstmt.setInt(2, sId);
			pstmt.setString(3, hotEditerType);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					int isTop = rs.getInt(1);
					String brandTitle = rs.getString(2);
					String firstCategoryText = rs.getString(3);
					int idSecondCategory = rs.getInt(4);
					String secondCategoryText = rs.getString(5);
					int idProduct = rs.getInt(6);
					String productTitle = rs.getString(7);
					String promotionText = rs.getString(8);
					String start_date = rs.getString(9);
					String end_date = rs.getString(10);
					int isDisplay = rs.getInt(11);
					result = new HotEditerCMD(idProduct, idSecondCategory, secondCategoryText, promotionText, isTop, isDisplay, 
							start_date, end_date, firstCategoryText, brandTitle, productTitle);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getHotEditer] " + e.getLocalizedMessage());
			logger.error("[getHotEditer] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getHotEditer][RETURN] result=" + result.toString());
		}
		logger.debug("[getHotEditer] ---------- Method End ----------");
		
		return result;
	}
	
	public ArrayList<HotEditerCMD> getHotEditerList(int page, int count, String order, String where) throws Exception {
		logger.debug("[getHotEditerList] ---------- Method Start ----------");
		logger.debug("[getHotEditerList][PARAM] page=" + page);
		logger.debug("[getHotEditerList][PARAM] count=" + count);
		logger.debug("[getHotEditerList][PARAM] order=" + order);
		logger.debug("[getHotEditerList][PARAM] where=" + where);
		
		ArrayList<HotEditerCMD> result = null;
		int cnt = db.getCount("hotediter h", "*", where);
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = null;
		if (order == null) {
			sql = "SELECT h.isTop, b.brandTitle, f.firstCategoryText, h.idSecondCategory, s.secondCategoryText, h.idProduct, p.productTitle, "
					+ "h.promotionText, h.start_date, h.end_date, h.isDisplay "
					+ "FROM hotediter h, product p, brand b, firstcategory f,secondcategory s "
					+ "WHERE h.idSecondCategory=s.idSecondCategory AND h.idProduct=p.idProduct AND "
					+ "p.idBrand=b.idBrand AND s.idFirstCategory=f.idFirstCategory AND "+ where + " "
					+ "GROUP BY h.create_date ORDER BY h.isTop DESC, h.create_date DESC "
					+ "LIMIT ?, ?";
		} else if (order != null) {
			sql = "SELECT h.isTop, b.brandTitle, f.firstCategoryText, h.idSecondCategory, s.secondCategoryText, h.idProduct, p.productTitle, "
					+ "h.promotionText, h.start_date, h.end_date, h.isDisplay "
					+ "FROM hotediter h, product p, brand b, firstcategory f,secondcategory s "
					+ "WHERE h.idSecondCategory=s.idSecondCategory AND h.idProduct=p.idProduct AND "
					+ "p.idBrand=b.idBrand AND s.idFirstCategory=f.idFirstCategory AND "+ where + " "
					+ "GROUP BY h.create_date ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<HotEditerCMD>();
				
				while(rs.next()) {
					int isTop = rs.getInt(1);
					String brandTitle = rs.getString(2);
					String firstCategoryText = rs.getString(3);
					int idSecondCategory = rs.getInt(4);
					String secondCategoryText = rs.getString(5);
					int idProduct = rs.getInt(6);
					String productTitle = rs.getString(7);
					String promotionText = rs.getString(8);
					String start_date = rs.getString(9);
					String end_date = rs.getString(10);
					int isDisplay = rs.getInt(11);
					HotEditerCMD item = new HotEditerCMD(idProduct, idSecondCategory, secondCategoryText, promotionText, isTop, isDisplay, 
							start_date, end_date, firstCategoryText, brandTitle, productTitle);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getHotEditerList] " + e.getLocalizedMessage());
			logger.error("[getHotEditerList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getHotEditerList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getHotEditerList] ---------- Method End ----------");
		
		return result;
	}
	
	/** 화제의 제품 xml */
	public ProductCMD getHotEditer(String hotEditerType) throws SQLException {
		logger.debug("[getHotEditer] ---------- Method Start ----------");
		logger.debug("[getHotEditer][PARAM] hotEditerType=" + hotEditerType);
		
		ProductCMD result = null;
		
		String sql = "SELECT p.idProduct, p.productTitle, b.brandTitle, p.volume, p.price, p.ratingAvg, p.rationCount, h.promotionText "
				+ "FROM hotediter h, product p, brand b "
				+ "WHERE h.isTop=1 AND h.idProduct=p.idProduct AND p.idBrand=b.idBrand AND h.hotEditerType=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, hotEditerType);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					int idProduct = rs.getInt(1);
					String productTitle = rs.getString(2);
					String brandTitle = rs.getString(3);
					String volume = rs.getString(4);
					int price = rs.getInt(5);
					double ratingAvg = rs.getDouble(6);
					int rationCount = rs.getInt(7);
					String promotionText = rs.getString(8);
					
					FileInfo image = this.getImage(idProduct);
					
					result = new ProductCMD(idProduct, brandTitle, productTitle, volume, price, ratingAvg, rationCount, promotionText, image);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getHotEditer] " + e.getLocalizedMessage());
			logger.error("[getHotEditer] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getHotEditer][RETURN] result=" + result.toString());
		}
		logger.debug("[getHotEditer] ---------- Method End ----------");
		
		return result;
	}
	
	/** 추천제품 xml */
	public ArrayList<ProductCMD> getHotEditer(String hotEditerType, int idSecondCategory) throws SQLException {
		logger.debug("[getHotEditer] ---------- Method Start ----------");
		logger.debug("[getHotEditer][PARAM] hotEditerType=" + hotEditerType);
		
		ArrayList<ProductCMD> result = null;
		
		int cnt = db.getCount("hotediter h, product p, brand b", "*", "h.idProduct=p.idProduct AND p.idBrand=b.idBrand AND h.hotEditerType='"
				+ hotEditerType + "' AND h.idSecondCategory=" + idSecondCategory);
		
		String sql = "SELECT p.idProduct, p.productTitle, b.brandTitle, p.volume, p.price, p.ratingAvg, p.rationCount, h.promotionText "
				+ "FROM hotediter h, product p, brand b "
				+ "WHERE h.idProduct=p.idProduct AND p.idBrand=b.idBrand AND h.hotEditerType=? AND h.idSecondCategory=? "
				+ "ORDER BY h.isTop DESC, h.create_date DESC";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		PageHandler.getInstance(1, cnt);
		
		try {
			pstmt.setString(1, hotEditerType);
			pstmt.setInt(2, idSecondCategory);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				result = new ArrayList<ProductCMD>();
				
				while (rs.next()) {
					int idProduct = rs.getInt(1);
					String productTitle = rs.getString(2);
					String brandTitle = rs.getString(3);
					String volume = rs.getString(4);
					int price = rs.getInt(5);
					double ratingAvg = rs.getDouble(6);
					int rationCount = rs.getInt(7);
					String promotionText = rs.getString(8);
					
					FileInfo image = this.getImage(idProduct);
					ProductCMD item = new ProductCMD(idProduct, brandTitle, productTitle, volume, price, ratingAvg, rationCount, promotionText, image);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getHotEditer] " + e.getLocalizedMessage());
			logger.error("[getHotEditer] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getHotEditer][RETURN] result=" + result.toString());
		}
		logger.debug("[getHotEditer] ---------- Method End ----------");
		
		return result;
	}
	
	public FileInfo getImage(int idx) throws SQLException {
		String fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType, sortKey "
				+ "FROM attachefile "
				+ "WHERE tableName='product' AND tableKey=? ORDER BY sortKey";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		FileInfo info = null;
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					int sortKey = rs.getInt(7);
					
					info = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType, sortKey);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return info;
	}
	
}
